﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace FYPDemo
{
    public class report
    {
        public static DataTable TransactionReportForSelectingOrder(string orderID, string DateFrom, string DateTo, string DatePaidFrom, string DatePaidTo, int Status, string memberName)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "TransactionReportForSelectingOrder";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = orderID;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = DateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = DateTo;

                cmd.Parameters.Add(new SqlParameter("@DatePaidFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DatePaidFrom"].Value = DatePaidFrom;

                cmd.Parameters.Add(new SqlParameter("@DatePaidTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DatePaidTo"].Value = DatePaidTo;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = Status;

                cmd.Parameters.Add(new SqlParameter("@memberName", SqlDbType.VarChar, 50));
                cmd.Parameters["@memberName"].Value = memberName;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable CostProfitReport(string Name, string DateFrom, string DateTo)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "CostProfitReport";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = Name;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = DateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = DateTo;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable SalesAnalysisReport(string memberName, string DateFrom, string DateTo, string ProductID, string orderBy)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "SalesAnalysisReport";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = memberName;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = DateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = DateTo;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 20));
                cmd.Parameters["@ProductID"].Value = ProductID;

                cmd.Parameters.Add(new SqlParameter("@orderBy", SqlDbType.VarChar, 20));
                cmd.Parameters["@orderBy"].Value = orderBy;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable OrderReportForVendor(string Vendor, string DateFrom, string DateTo)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "OrderReportForVendor";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Vendor", SqlDbType.VarChar, 50));
                cmd.Parameters["@Vendor"].Value = Vendor;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar,50));
                cmd.Parameters["@DateFrom"].Value = DateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = DateTo;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        public static DataTable MemberTransactionReport(string OrderID, string DateFrom, string DateTo)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "MemberTransactionReport";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@OrderID"].Value = OrderID;

                cmd.Parameters.Add(new SqlParameter("@DateFrom", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateFrom"].Value = DateFrom;

                cmd.Parameters.Add(new SqlParameter("@DateTo", SqlDbType.VarChar, 50));
                cmd.Parameters["@DateTo"].Value = DateTo;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return dt;
        }
    }
}